<?php

namespace App\Exports;

use App\Services\Designer\Model\DesignerDemand;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;

class DesignerDemandExport implements FromCollection, WithEvents , WithStrictNullComparison
{
    use Exportable;

    protected $params;
    protected $count;

    public function __construct(array $params)
    {
        $this->params = $params;
    }

    public function collection()
    {
        // 这里使用 collect 方法生成 collection，通常模型取出的数据即为 collection 格式
        $collection = new Collection($this->createData());
        return $collection;
    }

    protected function createData()
    {
        $heading = [
            ['设计师俱乐部需求数据表','','','','','','','',''],
            ['数据导出日期：'.date('Y年m月d日'),'','','','','','','',''],
            ['NO.','状态','标题','客户ID','单号','发布时间','计划完成时间','受理人','第一次完成时间','最后一次完成','方案审核确定','最高价格','备注说明'],
        ];
        $data = $this->queryData($this->params);
        $this->count = count($data);

        return array_merge($heading, $data);
    }

    protected function queryData($request)
    {
        $keywords = $request['keywords'] ?? '';
        $detail_status = $request['detail_status'] ?? '';
        $onum = $request['onum'] ?? '';
        $designer = $request['designer'] ?? '';
        $time_type = $request['time_type'] ?? '';
        $min = $request['min'] ?? '';
        $max = $request['max'] ?? '';
        $status = $request['status'] ?? '';
        $sort = $request['sort'] ?? 'created_at';
        $order = $request['order'] ?? 'desc';

        $datas = DesignerDemand::query()
            ->when($keywords, function ($query) use ($keywords) {
                return $query->where('title', 'like', '%' . $keywords . '%');
            })
            ->when($detail_status, function ($query) use ($detail_status) {
                return $query->where('detail_status', '=', $detail_status);
            })
            ->when($status, function ($query) use ($status) {
                return $query->where('status', '=', $status);
            })
            ->when($onum, function ($query) use ($onum) {
                return $query->where('onum', 'like', '%' . $onum . '%');
            })
            ->when($designer, function ($query) use ($designer) {
                return $query->whereHas('designer', function ($query) use ($designer) {
                    $query->where('kd_designers.name', 'like', "%{$designer}%");
                });
            })
            ->when(($time_type == 1) && $min && $max, function ($query) use ($min, $max) {
                return $query->where('created_at', '>=', $min)
                    ->where('created_at', '<=', $max);
            })
            ->when(($time_type == 2) && $min && $max, function ($query) use ($min, $max) {
                return $query->where('plan_finish_at', '>=', $min)
                    ->where('plan_finish_at', '<=', $max);
            })
            ->when(($time_type == 3) && $min && $max, function ($query) use ($min, $max) {
                return $query->where('first_finish_at', '>=', $min)
                    ->where('first_finish_at', '<=', $max);
            })
            ->when(($time_type == 4) && $min && $max, function ($query) use ($min, $max) {
                return $query->where('last_finish_at', '>=', $min)
                    ->where('last_finish_at', '<=', $max);
            })
            ->when(($time_type == 5) && $min && $max, function ($query) use ($min, $max) {
                return $query->where('verify_at', '>=', $min)
                    ->where('verify_at', '<=', $max);
            })
            ->orderBy("status", "asc")
            ->orderBy($sort, $order)
            ->get()->toArray();

        $res = [];
        foreach ($datas as $k=>$datum)
        {
            $res[$k][]= $k+1;
            $detail_status = "";
            if ($datum['detail_status'] == DesignerDemand::UNBID) {
                $detail_status = "寻找中-未有人投标";
            }
            if ($datum['detail_status'] == DesignerDemand::BIDED) {
                $detail_status = "寻找中-已有人投标";
            }
            if ($datum['detail_status'] == DesignerDemand::UNPROGRAMME) {
                $detail_status = "设计中-未上传方案";
            }
            if ($datum['detail_status'] == DesignerDemand::PROGRAMMED) {
                $detail_status = "设计中-已上传方案";
            }
            if ($datum['detail_status'] == DesignerDemand::FINISHED) {
                $detail_status = "已完工-已审核方案";
            }
            $res[$k][]= $detail_status;
            $res[$k][]= $datum['title'];

//            if (!$datum['onum']) {
//                $kcp = DB::table('k_crm_project as kcp')
//                    ->leftJoin('k_crm_order as crm_o', 'crm_o.id', '=', 'kcp.crm_oid')
//                    ->leftJoin('k_order as o', 'crm_o.oid', '=', 'o.id')
//                    ->select([
//                        'kcp.id as id',
//                        'kcp.crm_oid as crm_oid',
//                        'o.onum as onum',
//                    ])
//                    ->where('kcp.id', '=', $datum['k_crm_project_id'])
//                    ->first();
//                $datum['crm_oid'] = $kcp->crm_oid;
//                $datum['onum'] = $kcp->onum;
//
//                $designerDemand = DesignerDemand::findOrFail($datum['id']);
//                $designerDemand->crm_oid = $kcp->crm_oid;
//                $designerDemand->onum = $kcp->onum;
//                $designerDemand->save();
//            }
            $res[$k][]= $datum['crm_oid'];
            $res[$k][]= $datum['onum'];

            $res[$k][]= $datum['created_at'];
            $res[$k][]= $datum['plan_finish_at'];
            $res[$k][]= $datum['designer_name'];
            $res[$k][]= $datum['first_finish_at'];
            $res[$k][]= $datum['last_finish_at'];
            $res[$k][]= $datum['verify_at'];
            $res[$k][]= $datum['max_charge'];
            $res[$k][]= $datum['remark'];
        }

        return $res;
    }

    /**
     * 注册事件
     * @return array
     */
    public function registerEvents(): array
    {
        return [
            AfterSheet::class  => function(AfterSheet $event) {
                //设置列宽
                $event->sheet->getDelegate()->getColumnDimension('A')->setWidth(5);
                $event->sheet->getDelegate()->getColumnDimension('B')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('C')->setWidth(30);
                $event->sheet->getDelegate()->getColumnDimension('D')->setWidth(10);
                $event->sheet->getDelegate()->getColumnDimension('E')->setWidth(15);
                $event->sheet->getDelegate()->getColumnDimension('F')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('G')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('H')->setWidth(15);
                $event->sheet->getDelegate()->getColumnDimension('I')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('J')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('K')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('L')->setWidth(10);
                $event->sheet->getDelegate()->getColumnDimension('M')->setWidth(20);
                //设置区域单元格字体、颜色、背景等，其他设置请查看 applyFromArray 方法，提供了注释
                $event->sheet->getDelegate()->getStyle('A1:M1')->applyFromArray([
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                        'wrapText' => true,
                    ],
                    'font' => [
                        'name' => '等线',
                        'size' => '22',
                        'bold' => true,
                        'italic' => false,
                        'strikethrough' => false,
                    ],
                ]);
                $event->sheet->getDelegate()->getStyle('A2:M2')->applyFromArray([
                    'font' => [
                        'name' => 'Arial',
                        'bold' => true,
                        'italic' => false,
                        'strikethrough' => false,
                        'color' => [
                            'rgb' => 'FF0000'
                        ]
                    ]
                ]);
                $event->sheet->getDelegate()->getStyle('A3:M3')->applyFromArray([
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                        'wrapText' => true,
                    ],
                    'font' => [
                        'name' => 'Arial',
                        'bold' => true,
                        'italic' => false,
                        'strikethrough' => false,
                        'color' => [
                            'rgb' => '4472C4'
                        ]
                    ],
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => Border::BORDER_THIN,
                            'color' => [
                                'rgb' => '808080'
                            ]
                        ]
                    ],
                    'fill' => [
                        'fillType' => 'linear', //线性填充，类似渐变
                        'rotation' => 45, //渐变角度
                        'startColor' => [
                            'rgb' => 'F2F2F2' //初始颜色
                        ],
                        //结束颜色，如果需要单一背景色，请和初始颜色保持一致
                        'endColor' => [
                            'argb' => 'F2F2F2'
                        ]
                    ],
                ]);
                $event->sheet->getDelegate()->getStyle('A4:M'.(3+$this->count))->applyFromArray([
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                        'wrapText' => true,
                    ],
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => Border::BORDER_THIN,
                            'color' => [
                                'rgb' => '808080'
                            ]
                        ]
                    ],
                ]);

                //合并单元格
                $event->sheet->getDelegate()->mergeCells('A1:M1');
                $event->sheet->getDelegate()->mergeCells('A2:M2');
            }
        ];
    }
}
